"""
    本文档用于 项目数据(project_data) 页面
"""


# 查询所有的项目
all_project_name_search_sql = """
select 
        id, 
        name,
        status 
FROM
		zt_project
WHERE
		type in ('project', 'program')
AND
		project=0
AND
		parent=0
AND
		deleted='0'
AND
		status in ('doing', 'suspended', 'wait')
"""


# 项目测试单各个状态的统计
def construct_project_version_statistics(_project_ids):
    return f"""
SELECT
    pjectA.id,
	 pjectA.name, 
	count(IF(pj.`status`='doing',true,null)) as doing,
	count(IF(pj.`status`='suspended',true,null)) as suspended,
	count(IF(pj.`status`='wait',true,null)) as wait,
	count(IF(pj.`status`='closed',true,null)) as closed,
	count( 1 ) as total
FROM
	zt_project as pj 
LEFT JOIN
	zt_project as pject 
ON
	pject.id = SUBSTRING_INDEX(SUBSTRING_INDEX(pj.path,',',2), ',', -1)
LEFT JOIN
		zt_project as pjectA
ON
	pjectA.id = SUBSTRING_INDEX(SUBSTRING_INDEX(pject.path,',',2), ',', -1)
WHERE
	pj.type='sprint'
AND
	pjectA.id 
	{f'IN {_project_ids}' if len(_project_ids) > 1 else f'= {_project_ids[0]}' } 
AND
	pj.deleted='0'
GROUP BY
	pjectA.id
"""


# 项目Bug各个状态的统计
def construct_project_Bug_statistics(_project_ids):
    print(_project_ids)
    return f"""
SELECT
	pjectB.id as id, pjectB.name as name,
	count(
	IF
	( bug.`status` = 'closed', 1, null )) AS closed,
	count(
	IF
	( bug.`status` = 'resolved', 1, null )) AS resolved,
	count(
	IF
	( bug.`status` = 'active', 1, null )) AS active,
	count( 1 ) as total
FROM
	zt_bug AS bug
LEFT JOIN 
	zt_project AS pj 
ON 
    bug.project = pj.id
LEFT JOIN
	zt_project as pjectB 
ON 
	pjectB.id = SUBSTRING_INDEX(SUBSTRING_INDEX(pj.path,',',2), ',', -1)
WHERE
    pjectB.id
    {f'IN {_project_ids}' if len(_project_ids) > 1 else f'= {_project_ids[0]}' }
	AND bug.deleted = '0' 
GROUP BY
	pjectB.id
"""


# 未解决的Bug数
def construct_project_unsolved_Bug_statistics(_project_ids):
    return f"""
select 
		pjectB.id as id, pjectB.name as name,
    bug.`status`, count(1) as count
from 
    zt_bug as bug 
LEFT JOIN 
		zt_project AS pj 
ON 
		bug.project = pj.id 
LEFT JOIN
	zt_project as pjectB 
on 
	pjectB.id = SUBSTRING_INDEX(SUBSTRING_INDEX(pj.path,',',2), ',', -1)
WHERE
	pjectB.id 
	    {f'IN {_project_ids}' if len(_project_ids) > 1 else f'= {_project_ids[0]}' }
AND 
	bug.deleted = '0' 
AND
	bug.`status` = 'active'
GROUP BY
	pjectB.id
ORDER BY
    pjectB.id
"""


# 项目 Id 查询子系统
def construct_sql_search_subsystem_by_project_id(_id):
    return f"""
SELECT *
FROM
	zt_project as pj 
WHERE
	pj.deleted = '0'
AND
	pj.project = 0
AND
	pj.parent = {_id}
AND
	pj.type = 'project'
;
"""


# 查询所有的子系统
def  construct_sql_get_all_subsys_by_project_id(_id):
    return f"""
    SELECT 
			name, id
	FROM
		zt_project 
	WHERE
		deleted = '0'
	AND
		project = 0
	AND
		parent = {_id}
	AND
		type = 'project'
"""



# 依据项目id查询子系统的测试单与详情
def construct_sql_subsystem_detail(_id):
    return f"""
select al.subsys_id, al.task_name, al.begin, al.end as plan_deadline, al.testleader, al.`status`, al.`desc`,
			al.pj_name, al.pj_id
from
	(select 
	            tt.project as subsys_id, 
	            tt.`name` as task_name, 
	            tt.`begin`, 
	            tt.`end`, 
	            tt.`status`, 
	            tt.`desc`,
				pj.name as pj_name, 
				pj.id as pj_id, 
				pj.realname as testleader
	FROM	
		zt_testtask as tt 
	LEFT JOIN
		(SELECT 
			proj.name, proj.id, usrA.realname
        FROM
            zt_project as proj
        LEFT JOIN
            zentao.zt_user as usrA
        ON
            usrA.account = proj.QD
        WHERE
            proj.type = 'project'
        ) as pj
    on pj.id = tt.project
    WHERE
        pj.name is not NULL
    and
            pj.id {f'IN {_id}' if len(_id) > 1 else f'= {_id[0]}'}
    group by 
        tt.BEGIN
    ORDER BY
        tt.begin
	desc)
	as al
GROUP BY 
	al.pj_id
"""



# =================================   项目维度   =================================
# 按照项目id 统计未解决Bug
def construct_sql_system_unsolved_bug_statistics(_system_id):
    return f"""
select 
    	bug.project, `status`, count(*) as count
from 
    zt_bug as bug
LEFT JOIN
	zt_project as pj 
ON
	pj.id = bug.project
where 
    deleted='0'
and
    resolution = ''
AND
    SUBSTRING_INDEX(SUBSTRING_INDEX(pj.path,',', 2), ',',-1) = {_system_id}

"""


# 项目id维度
# 按照项目id计昨日新增未解决Bug
def construct_sql_system_unsolved_bug_daily_increase(_system_id):
    return f"""
select 
    	bug.project, bug.`status`, count(bug.id) as count
from 
    zt_bug as bug
LEFT JOIN
	zt_project as pj 
ON
	pj.id = bug.project
where 
    deleted='0'
and
    bug.resolution = ''
and 
    DATE(bug.`openedDate`) = DATE_SUB(CURRENT_DATE,INTERVAL 1 DAY)
AND
    SUBSTRING_INDEX(SUBSTRING_INDEX(pj.path,',', 2), ',',-1) = {_system_id}
"""


# 项目id维度
# 按照项目id获取半年每个月新增的Bug与关闭的Bug
def construct_system_bug_each_mouth_add_histogram_sql(_system_id, last_mouth=1):
    return f"""
select 
	count(bug.id) as count
from 
	zt_bug as bug
LEFT JOIN
	zt_project as pj 
ON
	pj.id = bug.project
where 
	bug.`deleted` = '0'
and 
	date(bug.`openedDate`) <= DATE_SUB(CURRENT_DATE,INTERVAL {last_mouth} MONTH)
and 
    date(bug.`openedDate`) > DATE_SUB(CURRENT_DATE,INTERVAL {last_mouth + 1} MONTH)
and 
    SUBSTRING_INDEX(SUBSTRING_INDEX(pj.path,',', 2), ',',-1) = {_system_id}
;
    """


# 项目id维度
# 每月新增 Bug 关闭 曲线图
def construct_system_bug_each_mouth_closed_line_sql(_system_id, last_mouth=1):
    return f"""
select 
	count(bug.id) as count
from 
	zt_bug as bug
LEFT JOIN
	zt_project as pj 
ON
	pj.id = bug.project
where 
	bug.`deleted` = '0'
and 
	date(bug.`closedDate`) <= DATE_SUB(CURRENT_DATE,INTERVAL {last_mouth} MONTH)
AND
	date(bug.`closedDate`) > DATE_SUB(CURRENT_DATE,INTERVAL {last_mouth + 1} MONTH)
and 
    SUBSTRING_INDEX(SUBSTRING_INDEX(pj.path,',', 2), ',',-1) = {_system_id}
    """



# # 子系统维度
# # 按照子系统获取半年每个月新增的 测试单 与关闭的 测试单
# # 新增测试单
# def construct_testtask_each_mouth_add_histogram_sql(_subsys_id, last_mouth=1):
#     return f"""
# select
# 	count(id) as count
# from
# 	zt_testtask as ttk
# where
# 	`deleted` = '0'
# and
# 	date(`begin`) <= DATE_SUB(CURRENT_DATE,INTERVAL {last_mouth} MONTH)
# and
#     date(`begin`) > DATE_SUB(CURRENT_DATE,INTERVAL {last_mouth + 1} MONTH)
# and
#     ttk.project = {_subsys_id}
# ;
#     """
#
#
#
# # 子系统维度
# # 每月新增 Bug 关闭 曲线图
# def construct_testtask_each_mouth_closed_line_sql(_subsys_id, last_mouth=1):
#     return f"""
# select
# 	count(id) as count
# from
# 	zt_testtask as ttk
# where
# 	`deleted` = '0'
# and
# 	date(`realFinishedDate`) <= DATE_SUB(CURRENT_DATE,INTERVAL {last_mouth} MONTH)
# and
#     date(`realFinishedDate`) > DATE_SUB(CURRENT_DATE,INTERVAL {last_mouth + 1} MONTH)
# and
#     ttk.project = {_subsys_id}
#
#     """





# ========================================   子系统维度   ========================================
# 按照子系统维度统计未解决Bug
def construct_sql_subsystem_unsolved_bug_statistics(_id):
    return f"""
select 
    	bug.project, `status`, count(*) as count
from 
    zt_bug as bug
where 
    deleted='0'
and
    resolution = ''
AND
    bug.project = {_id}

"""


# 子系统维度
# 按照子系统统计昨日新增未解决Bug
def construct_sql_subsystem_unsolved_bug_daily_increase(_id):
    return f"""
select 
    	bug.project, `status`, count(*) as count
from 
    zt_bug as bug
where 
    deleted='0'
and
    resolution = ''
and 
    DATE(`openedDate`) = DATE_SUB(CURRENT_DATE,INTERVAL 1 DAY)
AND
    bug.project = {_id}
"""


# 子系统维度
# 按照子系统获取半年每个月新增的Bug与关闭的Bug
def construct_subsystem_bug_each_mouth_add_histogram_sql(_subsys_id, last_mouth=1):
    return f"""
select 
	count(id) as count
from 
	zt_bug as bug
where 
	`deleted` = '0'
and 
	date(`openedDate`) <= DATE_SUB(CURRENT_DATE,INTERVAL {last_mouth} MONTH)
and 
    date(`openedDate`) > DATE_SUB(CURRENT_DATE,INTERVAL {last_mouth + 1} MONTH)
and 
    bug.project = {_subsys_id}
;
    """


# 子系统维度
# 每月新增 Bug 关闭 曲线图
def construct_subsystem_bug_each_mouth_closed_line_sql(_subsys_id, last_mouth=1):
    return f"""
select 
	count(id) as count
from 
	zt_bug as bug
where 
	`deleted` = '0'
and 
	date(`closedDate`) <= DATE_SUB(CURRENT_DATE,INTERVAL {last_mouth} MONTH)
AND
	date(`closedDate`) > DATE_SUB(CURRENT_DATE,INTERVAL {last_mouth + 1} MONTH)
and 
    bug.project = {_subsys_id}
    """




def construct_subsystem_testtask_status_statistics(_version_id):
    return f"""
select 
	count(IF(task.`status`='done',1,null)) as done,
	count(IF(task.`status`='blocked',1,null)) as blocked,
	count(IF(task.`status`='doing',1,null)) as doing,
	count(IF(task.`status`='wait',1,null)) as wait,
	count(task.id) as total
FROM
	zt_testtask as task 
WHERE
	task.execution = {_version_id}
"""





# 子系统维度
# 按照子系统获取半年每个月新增的 测试单 与关闭的 测试单
# 新增测试单
def construct_testtask_each_mouth_add_histogram_sql(_subsys_id, last_mouth=1):
    return f"""
select 
	count(id) as count
from 
	zt_testtask as ttk
where 
	`deleted` = '0'
and 
	date(`begin`) <= DATE_SUB(CURRENT_DATE,INTERVAL {last_mouth} MONTH)
and 
    date(`begin`) > DATE_SUB(CURRENT_DATE,INTERVAL {last_mouth + 1} MONTH)
and 
    ttk.project = {_subsys_id}
;
    """



# 子系统维度
# 每月新增 Bug 关闭 曲线图
def construct_testtask_each_mouth_closed_line_sql(_subsys_id, last_mouth=1):
    return f"""
select 
	count(id) as count
from 
	zt_testtask as ttk
where 
	`deleted` = '0'
and 
	date(`realFinishedDate`) <= DATE_SUB(CURRENT_DATE,INTERVAL {last_mouth} MONTH)
and 
    date(`realFinishedDate`) > DATE_SUB(CURRENT_DATE,INTERVAL {last_mouth + 1} MONTH)
and 
    ttk.project = {_subsys_id}

    """



# =============================================   迭代版本维度   =============================================
# 查询指定子系统的所有迭代版本中 测试单统计的详情
def construct_subsystem_all_iteration_version_detail(_subsystem_id):
 return f"""
SELECT
        pj.id as version_id,
		pj.`name`,
		pj.`begin` as start_date, 
		pj.`end` as end_date, 
		pj.days, 
		usrA.realname as RD, 
		usrB.realname as QD,
		pj.`status`,
		pj.`desc`
FROM
	    zt_project as pj 
LEFT JOIN
        zentao.zt_user as usrA
ON
        usrA.account = pj.RD
LEFT JOIN
        zentao.zt_user as usrB
ON
        usrB.account = pj.QD
WHERE
	pj.parent = {_subsystem_id}
"""



# 迭代版本维度
# 迭代版本统计未解决Bug
def construct_version_id_unsolved_bugs(_version_id):
    return f"""
SELECT
	exe.id, exe.`name`, count(1) as count
FROM
	zt_bug as bug 
LEFT JOIN
	(select 
* 
from 
	zt_project
WHERE
	type = 'sprint') as exe
ON
	exe.id = bug.execution

WHERE
	bug.deleted = '0'
AND
	exe.deleted = '0'
AND
	bug.resolution=''
AND
	exe.id = {_version_id}
;
"""


# 迭代版本维度
# 迭代版本统计每日新增Bug
def construct_version_id_daily_add_bugs(_version_id, last_day):
    return f"""
SELECT
	exe.id, exe.`name`, count(1) as count
FROM
	zt_bug as bug 
LEFT JOIN
	(select 
* 
from 
	zt_project
WHERE
	type = 'sprint') as exe
ON
	exe.id = bug.execution

WHERE
	bug.deleted = '0'
AND
	exe.deleted = '0'

AND
	bug.resolution=''
AND
	date(`begin`) <= DATE_SUB(CURRENT_DATE,INTERVAL {last_day} MONTH)
and 
  date(`begin`) > DATE_SUB(CURRENT_DATE,INTERVAL {last_day + 1} MONTH)
AND
	exe.id = {_version_id}
"""


# 迭代版本维度
# 迭代版本统计每日新增Bug 直方图
def construct_verison_bug_each_mouth_add_histogram_sql(_subsys_id, last_mouth=1):
    return f"""
select 
	count(id) as count
from 
	zt_bug as bug
where 
	`deleted` = '0'
and 
	date(`openedDate`) <= DATE_SUB(CURRENT_DATE,INTERVAL {last_mouth} MONTH)
and 
    date(`openedDate`) > DATE_SUB(CURRENT_DATE,INTERVAL {last_mouth + 1} MONTH)
and 
    bug.execution = {_subsys_id}
;
    """


# 迭代版本维度
# 迭代版本统计每月新增 Bug 关闭 曲线图
def construct_version_bug_each_mouth_closed_line_sql(_subsys_id, last_mouth=1):
    return f"""
select 
	count(id) as count
from 
	zt_bug as bug
where 
	`deleted` = '0'
and 
	date(`closedDate`) <= DATE_SUB(CURRENT_DATE,INTERVAL {last_mouth} MONTH)
AND
	date(`closedDate`) > DATE_SUB(CURRENT_DATE,INTERVAL {last_mouth + 1} MONTH)
and 
    bug.execution = {_subsys_id}
    """



def construct_verison_case_histogram_each_month(_version_id, last_month=0):
    return f"""
SELECT 
	count(run.id) as count,
		count(
	IF
	( run.lastRunResult = 'pass', 1, null )) AS pass,
	count(
	IF
	( run.lastRunResult = 'fail', 1, null )) AS fail,
	count(
	IF
	( run.lastRunResult = 'blocked', 1, null )) AS blocked
FROM
	zt_testrun as run 
LEFT JOIN
	zt_testtask as task 
ON
	task.id = run.task
WHERE
	task.execution={_version_id}
AND
	task.deleted = '0'
AND
	
	date(run.lastRunDate) <= DATE_SUB(CURRENT_DATE,INTERVAL {last_month} MONTH)
AND 
  date(run.lastRunDate) > DATE_SUB(CURRENT_DATE,INTERVAL {last_month + 1} MONTH)
"""



# 依据sys_id查询子系统的测试单与详情(for download)
def construct_sql_subsystem_detail_download(_sys_id):
    return f"""
select al.subsys_id, al.task_name, al.begin, al.end as plan_deadline, al.testleader, al.`status`, al.`desc`,
			al.pj_name, al.pj_id
from
	(select 
	            tt.project subsys_id, tt.`name` as task_name, tt.`begin`, tt.`end`, tt.`status`, tt.`desc`,
				pj.name as pj_name, pj.id as pj_id, pj.QD as testleader
	FROM	
		zt_testtask as tt 
	LEFT JOIN
		(SELECT 
			name, id, QD
	FROM
		zt_project 
	WHERE
		deleted = '0'
	AND
		project = 0
	AND
		type = 'project'
	) as pj
	on pj.id = tt.project
	WHERE
		pj.name is not NULL
	and
	    pj.id 
	{f'IN {_sys_id}' if len(_sys_id) > 1 else f'= {_sys_id[0]}'}
	group by 
		tt.BEGIN
	ORDER BY
		tt.begin
	desc) as al
GROUP BY 
	al.pj_id
"""




def construct_version_all_iteration_version_detail_download(_version_id):
 return f"""
SELECT
        pj.id as version_id,
		pj.`name`,
		pj.`begin` as start_date, 
		pj.`end` as end_date, 
		pj.days, 
		pj.RD, 
		pj.QD,
		pj.`status`,
		pj.`desc`
FROM
	zt_project as pj 
WHERE
	pj.id
	{f'IN {_version_id}' if len(_version_id) > 1 else f'= {_version_id[0]}'}
"""

























